<!DOCTYPE html>
<html lang="en">
<head>
  <title>MySQL - Born to build better enterprise frameworks and apps</title>
  <meta charset="utf-8">
  <meta name="description" content="index.description">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <link rel="icon" href="/images/favicon.png" type="image/x-icon">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/docsearch.js/2/docsearch.min.css" />
<link rel="stylesheet" href="/css/index.css">

    <script>
    !function(t,e,a,r,c){t.TracertCmdCache=t.TracertCmdCache||[],t[c]=window[c]||
      {_isInit:!0,call:function(){t.TracertCmdCache.push(arguments)},
      start:function(t){this.call('start',t)}},t[c].l=new Date;
      var n=e.createElement(a),s=e.getElementsByTagName(a)[0];
      n.async=!0,n.src=r,s.parentNode.insertBefore(n,s)}
    (window,document,'script','https://tracert.alipay.com/tracert.js','Tracert');
      Tracert.start({
        plugins: [ 'BucName' ],
        spmAPos: 'a454',
        spmBPos: 'b4893',
      });
    </script>
  
</head>
<body>
  <div class="nav" >
  <header>
    <a href="/en/" class="nav-logo leftpadding" alt="egg"><img src="https://zos.alipayobjects.com/rmsportal/VTcUYAaoKqXyHJbLAPyF.svg"></a>
    <ul class="nav-item">
      <li>
        <form id="search-form">
          <input type="text" id="search-query" class="search-query st-default-search-input">
        </form>
      </li>
      <li><a href="/en/intro/" alt="Guide">Guide</a></li><li><a href="/api/" alt="API">API</a></li><li><a href="/en/tutorials/index.html" alt="Tutorials">Tutorials</a></li><li><a href="https://github.com/search?q=topic%3Aegg-plugin&type=Repositories" alt="Plugins">Plugins</a></li><li><a href="https://github.com/eggjs/egg/releases" alt="Release">Release</a></li>
      
      
        <li class="translations">
          <a class="nav-link">中文文档</a>
          <span class="arrow"></span><ul id="dropdownContent" class="dropdown-content"><li><a id="en" href="/en/tutorials/mysql.html" style="color: #22ab28">English</a></li><li><a id="zh-cn" href="/zh-cn/tutorials/mysql.html" >中文</a></li></ul>
        </li>
      
      <li><iframe src="https://ghbtns.com/github-btn.html?user=eggjs&repo=egg&type=star&count=true" frameborder="0" scrolling="0" width="150px" height="20px"></iframe></li>
    </ul>
    <a id="mobileTrigger" href="#" class="mobile-trigger">
      <ul>
        <li></li>
        <li></li>
        <li></li>
      </ul>
    </a>
  </header>
</div>
  <div id="container" class="container">
    <div class="page-main">
  <article class="markdown-body">
    <h1>MySQL</h1>
    <p>MySQL is one of the most common and best RDBMS in terms of web applications. It is used in many large-scale websites such as Google and Facebook.</p>
<h2 id="egg-mysql"><a class="markdown-anchor" href="#egg-mysql">#</a> egg-mysql</h2>
<p>egg-mysql is provided to access both the MySQL databases and MySQL-based online database service.</p>
<h3 id="installation-and-configuration"><a class="markdown-anchor" href="#installation-and-configuration">#</a> Installation and Configuration</h3>
<p>Install <a href="https://github.com/eggjs/egg-mysql" target="_blank" rel="noopener">egg-mysql</a></p>
<figure class="highlight bash"><table><tr><td class="code"><pre><span class="line">$ npm i --save egg-mysql</span><br></pre></td></tr></table></figure>
<p>Enable Plugin:</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// config/plugin.js</span></span><br><span class="line">exports.mysql = &#123;</span><br><span class="line">  enable: <span class="literal">true</span>,</span><br><span class="line">  package: <span class="string">'egg-mysql'</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>Configure database information in <code>config/config.${env}.js</code></p>
<h4 id="single-data-source"><a class="markdown-anchor" href="#single-data-source">#</a> Single Data Source</h4>
<p>Configuration to accesss single MySQL instance as shown below:</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// config/config.$&#123;env&#125;.js</span></span><br><span class="line">exports.mysql = &#123;</span><br><span class="line">  <span class="comment">// database configuration</span></span><br><span class="line">  client: &#123;</span><br><span class="line">    host: <span class="string">'mysql.com'</span>,</span><br><span class="line">    port: <span class="string">'3306'</span>,</span><br><span class="line">    user: <span class="string">'test_user'</span>,</span><br><span class="line">    password: <span class="string">'test_password'</span>,</span><br><span class="line">    database: <span class="string">'test'</span>,</span><br><span class="line">  &#125;,</span><br><span class="line">  <span class="comment">// load into app, default true</span></span><br><span class="line">  app: <span class="literal">true</span>,</span><br><span class="line">  <span class="comment">// load into agent, default false</span></span><br><span class="line">  agent: <span class="literal">false</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>Use:</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">await</span> app.mysql.query(sql, values); <span class="comment">// single instance can be accessed through app.mysql</span></span><br></pre></td></tr></table></figure>
<h4 id="multiple-data-sources"><a class="markdown-anchor" href="#multiple-data-sources">#</a> Multiple Data Sources</h4>
<p>Configuration to accesss multiple MySQL instances as below:</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line">exports.mysql = &#123;</span><br><span class="line">  clients: &#123;</span><br><span class="line">    <span class="comment">// clientId, obtain the client instances using the app.mysql.get('clientId')</span></span><br><span class="line">    db1: &#123;</span><br><span class="line">      host: <span class="string">'mysql.com'</span>,</span><br><span class="line">      port: <span class="string">'3306'</span>,</span><br><span class="line">      user: <span class="string">'test_user'</span>,</span><br><span class="line">      password: <span class="string">'test_password'</span>,</span><br><span class="line">      database: <span class="string">'test'</span>,</span><br><span class="line">    &#125;,</span><br><span class="line">    db2: &#123;</span><br><span class="line">      host: <span class="string">'mysql2.com'</span>,</span><br><span class="line">      port: <span class="string">'3307'</span>,</span><br><span class="line">      user: <span class="string">'test_user'</span>,</span><br><span class="line">      password: <span class="string">'test_password'</span>,</span><br><span class="line">      database: <span class="string">'test'</span>,</span><br><span class="line">    &#125;,</span><br><span class="line">    <span class="comment">// ...</span></span><br><span class="line">  &#125;,</span><br><span class="line">  <span class="comment">//default configuration of all databases</span></span><br><span class="line">  <span class="keyword">default</span>: &#123;</span><br><span class="line"></span><br><span class="line">  &#125;,</span><br><span class="line"></span><br><span class="line">  <span class="comment">// load into app, default true</span></span><br><span class="line">  app: <span class="literal">true</span>,</span><br><span class="line">  <span class="comment">// load into agent, default false</span></span><br><span class="line">  agent: <span class="literal">false</span>,</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<p>Use:</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> client1 = app.mysql.get(<span class="string">'db1'</span>);</span><br><span class="line"><span class="keyword">await</span> client1.query(sql, values);</span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> client2 = app.mysql.get(<span class="string">'db2'</span>);</span><br><span class="line"><span class="keyword">await</span> client2.query(sql, values);</span><br></pre></td></tr></table></figure>
<h4 id="dynamic-creation"><a class="markdown-anchor" href="#dynamic-creation">#</a> Dynamic Creation</h4>
<p>Pre-declaration of configuration might not needed in the configuration file. Obtaining the actual parameters dynamically from the configuration center then initialize an instance instead.</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// &#123;app_root&#125;/app.js</span></span><br><span class="line"><span class="built_in">module</span>.exports = <span class="function"><span class="params">app</span> =&gt;</span> &#123;</span><br><span class="line">  app.beforeStart(<span class="keyword">async</span> () =&gt; &#123;</span><br><span class="line">    <span class="comment">// obtain the MySQL configuration from the configuration center</span></span><br><span class="line">    <span class="comment">// &#123; host: 'mysql.com', port: '3306', user: 'test_user', password: 'test_password', database: 'test' &#125;</span></span><br><span class="line">    <span class="keyword">const</span> mysqlConfig = <span class="keyword">await</span> app.configCenter.fetch(<span class="string">'mysql'</span>);</span><br><span class="line">    app.database = app.mysql.createInstance(mysqlConfig);</span><br><span class="line">  &#125;);</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>
<h2 id="service-layer"><a class="markdown-anchor" href="#service-layer">#</a> Service layer</h2>
<p>Connecting to MySQL is a data processing layer in the Web layer. So it is strongly recommended that keeping the code in the Service layer.</p>
<p>An example of connecting to MySQL as follows.</p>
<p>Details of Service layer, refer to <a href="../basics/service.html">service</a></p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// app/service/user.js</span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserService</span> <span class="keyword">extends</span> <span class="title">Service</span> </span>&#123;</span><br><span class="line">  <span class="keyword">async</span> find(uid) &#123;</span><br><span class="line">   <span class="comment">// assume we have the user id then trying to get the user details from database</span></span><br><span class="line">    <span class="keyword">const</span> user = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.get(<span class="string">'users'</span>, &#123; <span class="attr">id</span>: <span class="number">11</span> &#125;);</span><br><span class="line">    <span class="keyword">return</span> &#123; user &#125;;</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p>After that, obtaining the data from service layer using the controller</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// app/controller/user.js</span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">UserController</span> <span class="keyword">extends</span> <span class="title">Controller</span> </span>&#123;</span><br><span class="line">  <span class="keyword">async</span> info() &#123;</span><br><span class="line">    <span class="keyword">const</span> ctx = <span class="keyword">this</span>.ctx;</span><br><span class="line">    <span class="keyword">const</span> userId = ctx.params.id;</span><br><span class="line">    <span class="keyword">const</span> user = <span class="keyword">await</span> ctx.service.user.find(userId);</span><br><span class="line">    ctx.body = user;</span><br><span class="line">  &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h2 id="writing-crud"><a class="markdown-anchor" href="#writing-crud">#</a> Writing CRUD</h2>
<p>Following statments default under <code>app/service</code> if not specifed</p>
<h3 id="create"><a class="markdown-anchor" href="#create">#</a> Create</h3>
<p>INSERT method to perform the INSERT INTO query</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// INSERT</span></span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(<span class="string">'posts'</span>, &#123; <span class="attr">title</span>: <span class="string">'Hello World'</span> &#125;); <span class="comment">//insert a record title 'Hello World' to 'posts' table</span></span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`posts`</span>(<span class="string">`title`</span>) VALUES(<span class="string">'Hello World'</span>);</span><br><span class="line"></span><br><span class="line"><span class="built_in">console</span>.log(result);</span><br><span class="line">=&gt;</span><br><span class="line">&#123;</span><br><span class="line">  fieldCount: <span class="number">0</span>,</span><br><span class="line">  affectedRows: <span class="number">1</span>,</span><br><span class="line">  insertId: <span class="number">3710</span>,</span><br><span class="line">  serverStatus: <span class="number">2</span>,</span><br><span class="line">  warningCount: <span class="number">2</span>,</span><br><span class="line">  message: <span class="string">''</span>,</span><br><span class="line">  protocol41: <span class="literal">true</span>,</span><br><span class="line">  changedRows: <span class="number">0</span></span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// check if insertion is success or failure</span></span><br><span class="line"><span class="keyword">const</span> insertSuccess = result.affectedRows === <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h3 id="read"><a class="markdown-anchor" href="#read">#</a> Read</h3>
<p>Use <code>get</code> or <code>select</code> to select one or multiple records. <code>select</code> method support query criteria and result customization</p>
<ul>
<li>get one record</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> post = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.get(<span class="string">'posts'</span>, &#123; <span class="attr">id</span>: <span class="number">12</span> &#125;);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT * FROM <span class="string">`posts`</span> WHERE <span class="string">`id`</span> = <span class="number">12</span> LIMIT <span class="number">0</span>, <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>query all from the table</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.select(<span class="string">'posts'</span>);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT * FROM <span class="string">`posts`</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>query criteria and result customization</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.select(<span class="string">'posts'</span>, &#123; <span class="comment">// search posts table</span></span><br><span class="line">  where: &#123; <span class="attr">status</span>: <span class="string">'draft'</span>, <span class="attr">author</span>: [<span class="string">'author1'</span>, <span class="string">'author2'</span>] &#125;, <span class="comment">// WHERE criteria</span></span><br><span class="line">  columns: [<span class="string">'author'</span>, <span class="string">'title'</span>], <span class="comment">// get the value of certain columns</span></span><br><span class="line">  orders: [[<span class="string">'created_at'</span>,<span class="string">'desc'</span>], [<span class="string">'id'</span>,<span class="string">'desc'</span>]], <span class="comment">// sort order</span></span><br><span class="line">  limit: <span class="number">10</span>, <span class="comment">// limit the return rows</span></span><br><span class="line">  offset: <span class="number">0</span>, <span class="comment">// data offset</span></span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; SELECT <span class="string">`author`</span>, <span class="string">`title`</span> FROM <span class="string">`posts`</span></span><br><span class="line">  WHERE <span class="string">`status`</span> = <span class="string">'draft'</span> AND <span class="string">`author`</span> IN(<span class="string">'author1'</span>,<span class="string">'author2'</span>)</span><br><span class="line">  ORDER BY <span class="string">`created_at`</span> DESC, <span class="string">`id`</span> DESC LIMIT <span class="number">0</span>, <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h3 id="update"><a class="markdown-anchor" href="#update">#</a> Update</h3>
<p>UPDATE operation to update the records of databases</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="comment">// modify data and search by primary key ID, and refresh</span></span><br><span class="line"><span class="keyword">const</span> row = &#123;</span><br><span class="line">  id: <span class="number">123</span>,</span><br><span class="line">  name: <span class="string">'fengmk2'</span>,</span><br><span class="line">  otherField: <span class="string">'other field value'</span>,    <span class="comment">// any other fields u want to update</span></span><br><span class="line">  modifiedAt: <span class="keyword">this</span>.app.mysql.literals.now, <span class="comment">// `now()` on db server</span></span><br><span class="line">&#125;;</span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.update(<span class="string">'posts'</span>, row); <span class="comment">// update records in 'posts'</span></span><br><span class="line"></span><br><span class="line">=&gt; UPDATE <span class="string">`posts`</span> SET <span class="string">`name`</span> = <span class="string">'fengmk2'</span>, <span class="string">`modifiedAt`</span> = NOW() WHERE id = <span class="number">123</span> ;</span><br><span class="line"></span><br><span class="line"><span class="comment">// check if update is success or failure</span></span><br><span class="line"><span class="keyword">const</span> updateSuccess = result.affectedRows === <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">// if primary key is your custom id,such as custom_id,you should config it in `where`</span></span><br><span class="line"><span class="keyword">const</span> row = &#123;</span><br><span class="line">  name: <span class="string">'fengmk2'</span>,</span><br><span class="line">  otherField: <span class="string">'other field value'</span>,    <span class="comment">// any other fields u want to update</span></span><br><span class="line">  modifiedAt: <span class="keyword">this</span>.app.mysql.literals.now, <span class="comment">// `now()` on db server</span></span><br><span class="line">&#125;;</span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> options = &#123;</span><br><span class="line">  where: &#123;</span><br><span class="line">    custom_id: <span class="number">456</span></span><br><span class="line">  &#125;</span><br><span class="line">&#125;;</span><br><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.update(<span class="string">'posts'</span>, row, options); <span class="comment">// update records in 'posts'</span></span><br><span class="line"></span><br><span class="line">=&gt; UPDATE <span class="string">`posts`</span> SET <span class="string">`name`</span> = <span class="string">'fengmk2'</span>, <span class="string">`modifiedAt`</span> = NOW() WHERE custom_id = <span class="number">456</span> ;</span><br><span class="line"></span><br><span class="line"><span class="comment">// check if update is success or failure</span></span><br><span class="line"><span class="keyword">const</span> updateSuccess = result.affectedRows === <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h3 id="delete"><a class="markdown-anchor" href="#delete">#</a> Delete</h3>
<p>DELETE operation to delete the records of databases</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.delete(<span class="string">'posts'</span>, &#123;</span><br><span class="line">  author: <span class="string">'fengmk2'</span>,</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; DELETE FROM <span class="string">`posts`</span> WHERE <span class="string">`author`</span> = <span class="string">'fengmk2'</span>;</span><br></pre></td></tr></table></figure>
<h2 id="implementation-of-sql-statement"><a class="markdown-anchor" href="#implementation-of-sql-statement">#</a> Implementation of SQL statement</h2>
<p>Plugin supports splicing and execute SQL statment directly. It can use <code>query</code> to execute a valid SQL statement</p>
<p><strong>Note!! Strongly do not recommend developers splicing SQL statement, it is easier to cause SQL injection!!</strong></p>
<p>Use the <code>mysql.escape</code> method if you have to splice SQL statement</p>
<p>Refer to <a href="http://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js" target="_blank" rel="noopener">preventing-sql-injection-in-node-js</a></p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> postId = <span class="number">1</span>;</span><br><span class="line"><span class="keyword">const</span> results = <span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.query(<span class="string">'update posts set hits = (hits + ?) where id = ?'</span>, [<span class="number">1</span>, postId]);</span><br><span class="line"></span><br><span class="line">=&gt; update posts set hits = (hits + <span class="number">1</span>) where id = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
<h2 id="transaction"><a class="markdown-anchor" href="#transaction">#</a> Transaction</h2>
<p>Transaction is mainly used to deal with large data of high complexity. For example, in a personnel management system, deleting a person which need to delete the basic information of the staff, but also need to delete the related information of staff, such as mailboxes, articles and so on. It is easier to use transaction to run a set of operations.
A transaction is a set of continuous database operations which performed as a single unit of work. Each individual operation within the group is successful and the transaction succeeds. If one part of the transaction fails, then the entire transaction fails.
In gerenal, transaction must be atomic, consistent, isolated and durable.</p>
<ul>
<li>Atomicity requires that each transaction be &quot;all or nothing&quot;: if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged.</li>
<li>The consistency property ensures that any transaction will bring the database from one valid state to another.</li>
<li>The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially</li>
<li>The durability property ensures that once a transaction has been committed, it will remain so.</li>
</ul>
<p>Therefore, for a transaction, must be accompanied by beginTransaction, commit or rollback, respectively, beginning of the transaction, success and failure to roll back.</p>
<p>egg-mysql proviodes two types of transactions</p>
<h3 id="manual-control"><a class="markdown-anchor" href="#manual-control">#</a> Manual Control</h3>
<ul>
<li>adventage: <code>beginTransaction</code>, <code>commit</code> or <code>rollback</code> can be completely under control by developer</li>
<li>disadventage: more handwritten code, Forgot catching error or cleanup will lead to serious bug.</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> conn = <span class="keyword">await</span> app.mysql.beginTransaction(); <span class="comment">// initialize the transaction</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">try</span> &#123;</span><br><span class="line">  <span class="keyword">await</span> conn.insert(table, row1);  <span class="comment">// first step</span></span><br><span class="line">  <span class="keyword">await</span> conn.update(table, row2);  <span class="comment">// second step</span></span><br><span class="line">  <span class="keyword">await</span> conn.commit(); <span class="comment">// commit the transaction</span></span><br><span class="line">&#125; <span class="keyword">catch</span> (err) &#123;</span><br><span class="line">  <span class="comment">// error, rollback</span></span><br><span class="line">  <span class="keyword">await</span> conn.rollback(); <span class="comment">// rollback after catching the exception!!</span></span><br><span class="line">  <span class="keyword">throw</span> err;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h3 id="automatic-control-transaction-with-scope"><a class="markdown-anchor" href="#automatic-control-transaction-with-scope">#</a> Automatic control: Transaction with scope</h3>
<ul>
<li>API：<code>beginTransactionScope(scope, ctx)</code>
<ul>
<li><code>scope</code>: A generatorFunction which will execute all sqls of this transaction.</li>
<li><code>ctx</code>: The context object of current request, it will ensures that even in the case of a nested transaction, there is only one active transaction in a request at the same time.</li>
</ul>
</li>
<li>adventage: easy to use, as if there is no transaction in your code.</li>
<li>disadvantage: all transation will be successful or failed, cannot control precisely</li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> result = <span class="keyword">await</span> app.mysql.beginTransactionScope(<span class="keyword">async</span> conn =&gt; &#123;</span><br><span class="line">  <span class="comment">// don't commit or rollback by yourself</span></span><br><span class="line">  <span class="keyword">await</span> conn.insert(table, row1);</span><br><span class="line">  <span class="keyword">await</span> conn.update(table, row2);</span><br><span class="line">  <span class="keyword">return</span> &#123; <span class="attr">success</span>: <span class="literal">true</span> &#125;;</span><br><span class="line">&#125;, ctx); <span class="comment">// ctx is the context of current request, accessed by `this.ctx`  within in service file.</span></span><br><span class="line"><span class="comment">// if error throw on scope, will auto rollback</span></span><br></pre></td></tr></table></figure>
<h2 id="literal"><a class="markdown-anchor" href="#literal">#</a> Literal</h2>
<p>Use <code>Literal</code> if need to call literals or functions in MySQL</p>
<h3 id="inner-literal"><a class="markdown-anchor" href="#inner-literal">#</a> Inner Literal</h3>
<ul>
<li><code>NOW()</code>：The database system time, obtained by <code>app.mysql.literals.now</code></li>
</ul>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(table, &#123;</span><br><span class="line">  create_time: <span class="keyword">this</span>.app.mysql.literals.now,</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`$table`</span>(<span class="string">`create_time`</span>) VALUES(NOW())</span><br></pre></td></tr></table></figure>
<h3 id="custom-literal"><a class="markdown-anchor" href="#custom-literal">#</a> Custom literal</h3>
<p>The following demo showe how to call <code>CONCAT(s1, ...sn)</code> funtion in mysql to do string splicing.</p>
<figure class="highlight js"><table><tr><td class="code"><pre><span class="line"><span class="keyword">const</span> Literal = <span class="keyword">this</span>.app.mysql.literals.Literal;</span><br><span class="line"><span class="keyword">const</span> first = <span class="string">'James'</span>;</span><br><span class="line"><span class="keyword">const</span> last = <span class="string">'Bond'</span>;</span><br><span class="line"><span class="keyword">await</span> <span class="keyword">this</span>.app.mysql.insert(table, &#123;</span><br><span class="line">  id: <span class="number">123</span>,</span><br><span class="line">  fullname: <span class="keyword">new</span> Literal(<span class="string">`CONCAT("<span class="subst">$&#123;first&#125;</span>", "<span class="subst">$&#123;last&#125;</span>"`</span>),</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line">=&gt; INSERT INTO <span class="string">`$table`</span>(<span class="string">`id`</span>, <span class="string">`fullname`</span>) VALUES(<span class="number">123</span>, CONCAT(<span class="string">"James"</span>, <span class="string">"Bond"</span>))</span><br></pre></td></tr></table></figure>

  </article>
  <aside id="mobileAside" class="toc">
  <div class="mobile-menu">
    <ul>
      <li><a href="/en/intro/" alt="Guide">Guide</a></li><li><a href="/api/" alt="API">API</a></li><li><a href="/en/tutorials/index.html" alt="Tutorials">Tutorials</a></li><li><a href="https://github.com/search?q=topic%3Aegg-plugin&type=Repositories" alt="Plugins">Plugins</a></li><li><a href="https://github.com/eggjs/egg/releases" alt="Release">Release</a></li>
      
      
        <li class="translations">
          <a class="nav-link">中文文档</a>
          <span class="arrow"></span><ul id="dropdownContent" class="dropdown-content"><li><a id="en" href="/en/tutorials/mysql.html" style="color: #22ab28">English</a></li><li><a id="zh-cn" href="/zh-cn/tutorials/mysql.html" >中文</a></li></ul>
        </li>
      
    </ul>
  </div>
  <dl><dt id="title-Intro" style="cursor: pointer;" class="aside-title">Guide<a id="collapse-icon-Intro" class="icon opend"></a></dt><dd id=panel-Intro><ul><li><a href="/en/intro/index.html" class="menu-link">What is Egg?</a></li><li><a href="/en/intro/egg-and-koa.html" class="menu-link">Egg and Koa</a></li><li><a href="/en/intro/quickstart.html" class="menu-link">Quick Start</a></li><li><a href="/en/tutorials/progressive.html" class="menu-link">Progressive</a></li><li><a href="/en/migration.html" class="menu-link">Migration to 2.x</a></li></ul></dd><dt id="title-Basics" style="cursor: pointer;" class="aside-title">Basis Function<a id="collapse-icon-Basics" class="icon opend"></a></dt><dd id=panel-Basics><ul><li><a href="/en/basics/structure.html" class="menu-link">Directory Structure</a></li><li><a href="/en/basics/objects.html" class="menu-link">Built-in Objects</a></li><li><a href="/en/basics/env.html" class="menu-link">Environment</a></li><li><a href="/en/basics/config.html" class="menu-link">Configuration</a></li><li><a href="/en/basics/middleware.html" class="menu-link">Middleware</a></li><li><a href="/en/basics/router.html" class="menu-link">Router</a></li><li><a href="/en/basics/controller.html" class="menu-link">Controller</a></li><li><a href="/en/basics/service.html" class="menu-link">Service</a></li><li><a href="/en/basics/plugin.html" class="menu-link">Plugin</a></li><li><a href="/en/basics/schedule.html" class="menu-link">Schedule</a></li><li><a href="/en/basics/extend.html" class="menu-link">Extend</a></li><li><a href="/en/basics/app-start.html" class="menu-link">Custom Init</a></li></ul></dd><dt id="title-Core" style="cursor: pointer;" class="aside-title">Core<a id="collapse-icon-Core" class="icon opend"></a></dt><dd id=panel-Core><ul><li><a href="/en/core/development.html" class="menu-link">Development</a></li><li><a href="/en/core/unittest.html" class="menu-link">Unit Testing</a></li><li><a href="/en/core/deployment.html" class="menu-link">Deployment</a></li><li><a href="/en/core/logger.html" class="menu-link">Logger</a></li><li><a href="/en/core/httpclient.html" class="menu-link">HttpClient</a></li><li><a href="/en/core/cookie-and-session.html" class="menu-link">Cookie and Session</a></li><li><a href="/en/core/cluster-and-ipc.html" class="menu-link">Cluster and IPC</a></li><li><a href="/en/core/view.html" class="menu-link">View</a></li><li><a href="/en/core/error-handling.html" class="menu-link">Error Handling</a></li><li><a href="/en/core/security.html" class="menu-link">Security</a></li><li><a href="/en/core/i18n.html" class="menu-link">i18n</a></li></ul></dd><dt id="title-Tutorials" style="cursor: pointer;" class="aside-title">Tutorials<a id="collapse-icon-Tutorials" class="icon opend"></a></dt><dd id=panel-Tutorials><ul><li><a href="/en/tutorials/mysql.html" class="menu-link">MySQL</a></li><li><a href="/en/tutorials/restful.html" class="menu-link">RESTful API</a></li><li><a href="/en/tutorials/passport.html" class="menu-link">Passport</a></li><li><a href="/en/tutorials/socketio.html" class="menu-link">Socket.IO</a></li><li><a href="/en/tutorials/assets.html" class="menu-link">Assets</a></li><li><a href="/en/tutorials/typescript.html" class="menu-link">TypeScript</a></li></ul></dd><dt id="title-Advanced" style="cursor: pointer;" class="aside-title">Advanced<a id="collapse-icon-Advanced" class="icon opend"></a></dt><dd id=panel-Advanced><ul><li><a href="/en/advanced/loader.html" class="menu-link">Loader</a></li><li><a href="/en/advanced/plugin.html" class="menu-link">Plugin Development</a></li><li><a href="/en/advanced/framework.html" class="menu-link">Framework</a></li><li><a href="/en/advanced/cluster-client.html" class="menu-link">Cluster Enhancement</a></li><li><a href="/en/advanced/view-plugin.html" class="menu-link">View Plugin</a></li><li><a href="/en/style-guide.html" class="menu-link">Style Guide</a></li></ul></dd><dt id="title-Community" style="cursor: pointer;" class="aside-title">Community<a id="collapse-icon-Community" class="icon opend"></a></dt><dd id=panel-Community><ul><li><a href="/en/plugins/" class="menu-link">Plugin List</a></li><li><a href="/en/contributing.html" class="menu-link">Contributing</a></li><li><a href="/en/resource.html" class="menu-link">Resource</a></li><li><a href="/en/faq.html" class="menu-link">FAQ</a></li></ul></dd></dl>
</aside>
<script>
var mobileTrigger = document.getElementById('mobileTrigger');
var mobileAside = document.getElementById('mobileAside');

var expandMenu = function(title) {
  // handle icon
  const collapseIcon = document.getElementById('collapse-icon-' + title);
  if (collapseIcon) {
    collapseIcon.className = 'icon opend';
  }
  // handle panelEle
  const panelEle = document.getElementById('panel-' + title);
  if (panelEle) {
    panelEle.className = '';
  }
}

var collapseMenu = function(title) {
  // handle icon
  const collapseIcon = document.getElementById('collapse-icon-' + title);
  if (collapseIcon) {
    collapseIcon.className = 'icon closed';
  }
  // handle panelEle
  const panelEle = document.getElementById('panel-' + title);
  if (panelEle) {
    panelEle.className = 'aside-panel-hidden';
  }
}

mobileAside.onclick = function(e) {
  const targetId = e.target.id;
  if (targetId && (targetId.indexOf('title-') > -1 || targetId.indexOf('collapse-icon-') > -1)) {
    const title = targetId.replace('title-', '').replace('collapse-icon-', '');
    try { 
      // the the browser may have no localStroage or JSON.parse may throw exception.
      const menuInfo = JSON.parse(window.localStorage.getItem('menuInfo'));
        
      // current menu status
      const curClosed = menuInfo[title] ? menuInfo[title].closed : false; // default false

      // change UI
      curClosed ? expandMenu(title) : collapseMenu(title);

      // save menuInfo to localStorage
      menuInfo[title] = { closed: !curClosed } // opposite
      window.localStorage.setItem('menuInfo', JSON.stringify(menuInfo));
    } catch (e) {}
  }
};

mobileTrigger.onclick = function(e) {
  e.preventDefault();
  if (mobileAside.className.indexOf('mobile-show') === -1) {
    mobileAside.className += ' mobile-show';
  } else {
    mobileAside.className = 'toc';
  }
};

(function() {
  // save data to localStorage because the page will refresh when user change the url.
  let menuInfo;
  try { 
    // the the browser may have no localStroage or JSON.parse may throw exception.
    menuInfo = JSON.parse(window.localStorage.getItem('menuInfo'));
    if (!menuInfo) {
      menuInfo = {};
      window.localStorage.setItem('menuInfo', JSON.stringify(menuInfo));
    }
  } catch (e) {
    menuInfo = {}; // default {}
  }

  for (const title in menuInfo) {
    if (menuInfo[title] && menuInfo[title].closed) { // menu in closed status.
      collapseMenu(title);
    } else {
      expandMenu(title);
    }
  }

  // highlight menu
  const pathname = window.location.pathname;
  const selector = `a[href="${pathname}"].menu-link,a[href="${pathname}index.html"].menu-link`;
  const menuItem = mobileAside.querySelector(selector);
  if (menuItem) { menuItem.className += ' highlight'; }
})();
</script>

</div>

  </div>
</body>
<script src="https://cdn.jsdelivr.net/docsearch.js/2/docsearch.min.js"></script>
<script>
docsearch({
  apiKey: '1561de31a86f79507ea00cdb54ce647c',
  indexName: 'eggjs',
  inputSelector: '#search-query',
});
</script>
<div class="cnzz">
<script src="https://s11.cnzz.com/z_stat.php?id=1261142226&web_id=1261142226" language="JavaScript"></script>
</div>

</html>
